-- buffer pool usage per database
SELECT 
   (CASE WHEN (database_id = 32767)
        THEN 'Resource Database'
        ELSE DB_NAME(database_id) END) AS database_name,
    COUNT(*) * 8 / 1024 AS used_space_mb,
    SUM(CAST(free_space_in_bytes AS BIGINT)) / POWER(1024, 2) AS free_space_mb
 FROM sys.dm_os_buffer_descriptors
 WHERE database_id > 4
 GROUP BY database_id;
 GO

-- break things down by table and index across all databases that are using space in the buffer pool
EXECUTE sp_MSforeachdb 
N'IF DB_ID(''?'') > 4
	AND EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME(database_id) AS database_name 
	FROM sys.dm_os_buffer_descriptors) AS n WHERE database_name = ''?'')
 BEGIN
	 USE [?];
	 SELECT
		 ''?'' AS database_name,
		 OBJECT_NAME(p.object_id) AS object_name,
		 p.index_id,
		 i.name AS index_name,
		 i.type_desc AS index_type,
		 au.type_desc AS allocation_unit_type,
		 (dirty_page_count + clean_page_count) * 8 / 1024 AS total_pages_mb,
		 (dirty_pages_free_space + clean_pages_free_space) / POWER(1024, 2) AS free_space_mb,
		 CAST(ROUND(100.0 * ((dirty_pages_free_space + clean_pages_free_space) / 1024) / ((dirty_page_count + clean_page_count) * 8), 2) AS DECIMAL (4, 2)) AS free_space_percent
	 FROM
		 (SELECT
			 allocation_unit_id,
			 SUM (CASE WHEN (is_modified = 1)
				 THEN 1 ELSE 0 END) AS dirty_page_count, 
			SUM (CASE WHEN (is_modified = 1)
				 THEN 0 ELSE 1 END) AS clean_page_count,
			SUM (CASE WHEN (is_modified = 1)
				 THEN CAST(free_space_in_bytes AS BIGINT) ELSE 0 END) AS dirty_pages_free_space, 
			SUM (CASE WHEN (is_modified = 1)
				 THEN 0 ELSE CAST(free_space_in_bytes AS BIGINT) END) AS clean_pages_free_space
		 FROM sys.dm_os_buffer_descriptors
		 WHERE database_id = DB_ID(''?'')
		 GROUP BY allocation_unit_id) AS bd
	 INNER JOIN sys.allocation_units AS au
		 ON au.allocation_unit_id = bd.allocation_unit_id
	 INNER JOIN sys.partitions AS p
		 ON au.container_id = p.partition_id
	 INNER JOIN sys.indexes AS i
		 ON i.index_id = p.index_id AND p.object_id = i.object_id
		 WHERE p.object_id > 100
		 --AND (dirty_page_count + clean_page_count) > 12800
	 ORDER BY free_space_percent DESC;
  END';

-- buffer pool usage in a single database
USE <database_name>;
GO

SELECT
	s.name AS schema_name,
    o.name AS table_name,
    p.index_id,
    i.name AS index_name,
    i.type_desc AS index_type,
	dirty_page_count,
	clean_page_count,
	dirty_page_count * 8 / 1024 AS dirty_pages_mb,
	clean_page_count * 8 / 1024 AS clean_pages_mb,
    (dirty_page_count + clean_page_count) * 8 / 1024 AS total_pages_mb,
	dirty_pages_free_space_bytes / POWER(1024, 2) AS dirty_pages_free_space_mb,
	clean_pages_free_space_bytes / POWER(1024, 2) AS clean_pages_free_space_mb,
    (dirty_pages_free_space_bytes + clean_pages_free_space_bytes) / POWER(1024, 2) AS free_space_mb,
    CAST(ROUND(100.0 * ((dirty_pages_free_space_bytes + clean_pages_free_space_bytes) / 1024) /
		((dirty_page_count + clean_page_count) * 8), 2) AS DECIMAL (4, 2)) AS free_space_percent
FROM
    (SELECT
        allocation_unit_id,
        SUM(CASE WHEN (is_modified = 1)
            THEN 1 ELSE 0 END) AS dirty_page_count, 
        SUM(CASE WHEN (is_modified = 1)
            THEN 0 ELSE 1 END) AS clean_page_count,
        SUM(CASE WHEN (is_modified = 1)
            THEN CAST(free_space_in_bytes AS BIGINT) ELSE 0 END) AS dirty_pages_free_space_bytes, 
        SUM(CASE WHEN (is_modified = 1)
            THEN 0 ELSE CAST(free_space_in_bytes AS BIGINT) END) AS clean_pages_free_space_bytes
    FROM sys.dm_os_buffer_descriptors
    WHERE database_id = DB_ID()
    GROUP BY allocation_unit_id) AS bd
INNER JOIN sys.allocation_units AS au
    ON au.allocation_unit_id = bd.allocation_unit_id
INNER JOIN sys.partitions AS p
    ON au.container_id = p.partition_id
INNER JOIN sys.indexes AS i
    ON i.index_id = p.index_id AND p.object_id = i.object_id
INNER JOIN sys.objects AS o
    ON o.object_id = i.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
AND p.object_id > 100
--AND (dirty_page_count + clean_page_count) > 12800  -- taking up more than 100MB
ORDER BY free_space_mb DESC;